<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>SQLite expressions</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="SQLite, SQL, database, expressions">
<meta name="description" content="In this part of the SQLite tutorial we will
cover expressions.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content2">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>SQLite expressions</h1>


<p>
In this part of the SQLite tutorial, we will cover SQLite expressions. 
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
Wikipedia defines an expression in a programming language as a combination of values, 
variables, operators, and functions that are interpreted (evaluated) 
according to the particular rules of precedence and of association for
a particular programming language, which computes and then produces 
(returns, in a stateful environment) another value. The expression is
said to evaluate to that value. 
</p>


<h2>Literal values</h2>

<p>
A literal value is a constant of some kind. Literal values may be integers, 
floating point numbers, strings, BLOBs, or NULLs.
</p>

<pre class="code">
sqlite> SELECT 3, 'Wolf', 34.5;
3|Wolf|34.5
</pre>

<p>
Here we return three literals. Namely integer, string and floating point constants. 
</p>

<pre class="code">
sqlite> .nullvalue NULL
sqlite> SELECT NULL;
NULL
</pre>

<p>
The <b>.nullvalue</b> command tells the SQLite to show <b>NULL</b> values as 
<b>NULL</b>. SQLite shows empty strings for <b>NULL</b> values by default. 
<b>NULL</b> value is a literal too. 
</p>

<pre class="code">
sqlite> SELECT x'345eda2348587aeb';
x'345eda2348587aeb'
-------------------
</pre>

<p>
<b>BLOB</b> literals are string literals containing hexadecimal data and preceded 
by a single "x" or "X" character.
</p>


<h2>Operators</h2>

<p>
<b>Operators</b> are used to build expressions. SQL operators 
are very similar to mathematical operators. There are two kinds of operators. 
Binary and unary. Binary operators work with two operands, unary work with one. 
An operator may have one or two operands. An <b>operand</b> 
is one of the inputs (arguments) of an operator. 
</p>

<p>
There have several types of operators:
</p>

<ul>
    <li>Arithmetic operators</li>
    <li>Boolean operators</li>
    <li>Relational operators</li>
    <li>Bitwise operators</li>
    <li>Other operators</li>
</ul>


<p>
SQLite understand the following binary operators. 
</p>

<pre class="code">
||
*    /    %
+    -
&lt;&lt;   &lt;&gt;   &amp;    |
&lt;    &lt;=   &gt;    &gt;=
=    ==   !=   &lt;&gt;   IS  IN  LIKE  GLOB  BETWEEN
AND   
OR
</pre>

<p>
Operators are arranged according to the precedence. 
The <b>||</b> operator has the highest order of precedence, 
the <b>OR</b> operator the lowest. 
</p>

<p>
These are the unary prefix operators:
</p>

<pre class="code">
-    +    ~    NOT
</pre>

<p>
The <b>+</b> is a no-op. It does not do anything. The 
<b>-</b> unary operator changes positive values
to negative and vice versa. 
</p>

<pre class="code">
sqlite> SELECT -(3-44);
41
</pre>
 
<p>
The result is 41. The other two operators will be discussed later on.
</p>

<h3>Arithmetic operators</h3>

<p>
Arithmetic operators understood by SQLite are: multiplication, 
division, addition, subtraction and modulo. 
</p>

<pre class="code">
sqlite> SELECT 3*3/9;
1
</pre>

<p>
These are multiplication and division operators, that we know from mathematics. 
</p>

<pre class="code">
sqlite> SELECT 3 + 4 - 1 + 5;
11
</pre>

<p>
We show the addition and subtraction operators. 
</p>

<pre class="code">
sqlite> SELECT 11 % 3;
2
</pre>

<p>
The <b>%</b> operator is called the modulo operator. It finds the 
remainder of division of one number by another. 11 % 3, 11 modulo 3 is 2, 
because 3 goes into 11 three times with a remainder of 2. 
</p>

<h3>Boolean operators</h3>

<p>
With boolean operators we perform logical operations. SQLite has the following
boolean operators: <b>AND</b>, <b>OR</b> and <b>NOT</b>.
Boolean operators return true or false. In SQLite, 1 is true, 0 is false. 
</p>

<p>
The <b>AND</b> operator evaluates to true, if both operands are true. 
</p>

<pre class="code">
sqlite> SELECT 0 AND 0, 0 AND 1, 1 AND 0, 1 AND 1;
0|0|0|1
</pre>

<p>
The first three operations evaluate to false, the last one to true. 
</p>


<pre class="code">
sqlite> SELECT 3=3 AND 4=4;
1
</pre>

<p>
Both operands are true, so the result is true (1).
</p>

<p>
The <b>OR</b> operator evaluates to true, if at least one 
of the operands is true. 
</p>

<pre class="code">
sqlite> SELECT 0 OR 0, 0 OR 1, 1 OR 0, 1 OR 1;
0|1|1|1
</pre>

<p>
The first operation evaluates to false, other operations evaluate to true.
</p>

<p>
The <b>NOT</b> operator is negation operator. It makes true false and 
false true. 
</p>

<pre class="code">
sqlite> SELECT NOT 1, NOT 0;
0|1
sqlite> SELECT NOT (3=3);
0
</pre>

<h3>Relational operators</h3>

<p>
Relational operators are used to compare values. 
</p>

<table>
<tbody>
<tr>
<th>Symbol</th><th>Meaning</th>
</tr>
<tr><td>&lt;</td><td>strictly less than</td></tr>
<tr class="gray"><td>&lt;=</td><td>less than or equal to</td></tr>
<tr><td>&gt;</td><td>greater than</td></tr>
<tr class="gray"><td>&gt;=</td><td>greater than or equal to</td></tr>
<tr><td>= or ==</td><td>equal to</td></tr>
<tr class="gray"><td>!= or &lt;&gt;</td><td>not equal to</td></tr>
</tbody>
</table>

<p>
These operators always result in a boolean value. 
</p>

<pre class="code">
sqlite> SELECT 3*3 == 9, 9 = 9;
1|1
</pre>

<p>
Both <b>=</b> and <b>==</b> are equality operators. 
</p>

<pre class="code">
sqlite> SELECT 3 &lt; 4, 3 &lt;&gt; 5, 4 &gt;= 4, 5 != 5;
1|1|1|0
</pre>

<p>
Usage of the relational operators is known from mathematics. 
</p>

<h3>Bitwise operators</h3>

<p>
Decimal numbers are natural to humans. Binary numbers are native to comptuters. 
Binary, octal, decimal or hexadecimal symbols are only notations of the same number.
Bitwise operators work with bits of a binary number. We have binary logical 
operators and shift operators.
</p>

<p>
The <b>bitwise and operator</b> performs bit-by-bit comparison between two nubmers. 
The result for a bit position is 1 only if both corresponding bits in the operands are 1. 
</p>

<pre>
    00110
  &amp; 00011
  = 00010
</pre>

<p>
The first number is a binary notation of 6. The second is 3. The result is 2. 
</p>

<pre class="code">
sqlite> SELECT 6 &amp; 3;
2
sqlite> SELECT 3 &amp; 6;
2
</pre>

<p>
The <b>bitwise or operator</b> performs bit-by-bit comparison between two nubmers.
The result for a bit position is 1 if either of the corresponding bits in the operands is 1. 
</p>

<pre>
     00110
  |  00011
   = 00111
</pre>

<p>
The result is <code>00110</code> or decimal 7. 
</p>

<pre class="code">
sqlite> SELECT 6 | 3;
7
</pre>


<p>
The <b>bitwise shift operators</b> shift bits to the right or left. 
</p>

<pre>
number &lt;&lt; n : multiply number 2 to the nth power
number &gt;&gt; n : divide number by 2 to the nth power
</pre>

<p>
These operators are also called arithmetic shift. 
</p>

<pre>
     00110
 &gt;&gt;  00001
   = 00011
</pre>

<p>
We shift each of the bits of number six to the right. It is equal to dividing the six by 2.
The result is <code>00011</code> or decimal 3. 
</p>

<pre class="code">
sqlite> SELECT 6 >> 1;
3
</pre>

<pre>
     00110
  &lt;&lt; 00001
   = 01100
</pre>

<p>

We shift each of the bits of number six to the left. It is equal to muliplying the number six by 2.
The result is <code>01100</code> or decimal 12. 
</p>

<pre class="code">
sqlite> SELECT 6 &lt;&lt; 1;
12
</pre>

<p>
The <b>bitwise negation operator</b> changes each 1 to 0 and 0 to 1. It is also called 
the tilda operator.
</p>

<pre class="code">
sqlite> SELECT ~7;
-8
sqlite> SELECT ~-8;
7
</pre>

<p>
The operator reverts all bits of a number 7. One of the bits also determines, 
whether the number is negative or not. 
If we negate all the bits one more time, we get number 7 again. 
</p>


<h3>Other operators</h3>

<p>
There are some other operators left. These include <b>||</b>, <b>IN</b>, <b>LIKE</b>, 
<b>GLOB</b>, <b>BETWEEN</b>. 
</p>

<pre class="code">
sqlite> SELECT 'wolf' || 'hound';
wolfhound
</pre>

<p>
The <b>||</b> operator is a string concatenation operator. It simply joins strings. 
</p>

<p>
We can use the <b>IN</b> operator in two cases. 
</p>

<pre class="code">
sqlite> SELECT 'Tom' IN ('Tom', 'Frank', 'Jane');
1
</pre>

<p>
Here we check, if the string value 'Tom' is in the list of names, 
following the <b>IN</b> operator. The return value is a boolean value. 
</p>

<p>
For the following example, let's recap, what we have in the Cars table. 
</p>

<pre class="code">
sqlite> SELECT * FROM Cars;
Id          Name        Cost      
----------  ----------  ----------
1           Audi        52642     
2           Mercedes    57127     
3           Skoda       9000      
4           Volvo       29000     
5           Bentley     350000    
6           Citroen     21000     
7           Hummer      41400     
8           Volkswagen  21600  
</pre>

<p>
In the second case, the <b>IN</b> operator allows you 
to specify multiple values in a <b>WHERE</b> clause.
</p>

<pre class="code">
sqlite> SELECT * FROM Cars WHERE Name IN ('Audi', 'Hummer');
Id          Name        Cost      
----------  ----------  ----------
1           Audi        52642     
7           Hummer      41400 
</pre>

<p>
From the Cars table, we choose cars, that are listed after the <b>IN</b> operator.
</p>

<p>
The <b>LIKE</b> operator is used in a <b>WHERE</b> clause to search 
for a specified pattern in a column.
</p>

<pre class="code">
sqlite>  SELECT * FROM Cars WHERE Name LIKE 'Vol%';
Id          Name        Cost      
----------  ----------  ----------
4           Volvo       29000     
8           Volkswagen  21600   
</pre>

<p>
Here we select cars, whose names begin with 'Vol'.
</p>

<pre class="code">
sqlite> SELECT * FROM Cars WHERE Name LIKE '____';
Id          Name        Cost      
----------  ----------  ----------
1           Audi        52642      
</pre>

<p>
Here we select a car name, that has exactly four characters. There are four
underscores. 
</p>

<p>
The <b>GLOB</b> operator is similar to the <b>LIKE</b>, but uses 
the Unix file globbing syntax for its wildcards. Also, <b>GLOB</b> is case sensitive, 
unlike <b>LIKE</b>.
</p>

<pre class="code">
sqlite> SELECT * FROM Cars WHERE Name GLOB '*en';
Id          Name        Cost      
----------  ----------  ----------
6           Citroen     21000     
8           Volkswagen  21600 
</pre>

<p>
Here we have cars, whose names end with 'en' characters.  
</p>

<pre class="code">
sqlite> SELECT * FROM Cars WHERE Name GLOB '????';
Id          Name        Cost      
----------  ----------  ----------
1           Audi        52642 
</pre>

<p>
Here we again select a car name, that has exactly four characters.
</p>

<pre class="code">
sqlite> SELECT * FROM Cars WHERE Name GLOB '*EN';
sqlite> SELECT * FROM Cars WHERE Name LIKE '%EN';
Id          Name        Cost      
----------  ----------  ----------
6           Citroen     21000     
8           Volkswagen  21600  
</pre>

<p>
These two statements demonstrate, that the <b>LIKE</b> operator
is case insensitive, <b>GLOB</b> is case sensitive.
</p>

<p>
The <b>BETWEEN</b> operator is equivalent to a pair of comparisons. 
a BETWEEN b AND c is equivalent to a&gt;=b AND a&lt;=c
</p>

<pre class="code">
sqlite> SELECT * FROM Cars WHERE Cost BETWEEN 20000 AND 55000;
Id          Name        Cost      
----------  ----------  ----------
1           Audi        52642     
4           Volvo       29000     
6           Citroen     21000     
7           Hummer      41400     
8           Volkswagen  21600  
</pre>

<p>
In this SQL statement, we have selected cars, which cost between 20000 and 55000 units. 
</p>


<h2>Precedence</h2>

<p>
The <b>operator precedence</b> tells us which operators are evaluated first. 
The precedence level is necessary to avoid ambiguity in expressions. 

</p>

<p>
What is the outcome of the following expression? 28 or 40?
</p>

<pre>
3 + 5 * 5
</pre>

<p>
Like in mathematics, the multiplication operator has a higher 
precedence than addition operator. So the outcome is 28.
</p>

<pre>
(3 + 5) * 5
</pre>

<p>
To change the order of evaluation, we can use square brackets. 
Expressions inside square brackets are always evaluated first. 
</p>


<pre class="code">
sqlite> SELECT 3+5*5, (3+5)*5;
28|40
</pre>

<p>
The first expression evaluates to 28, because the multiplication operator has a higher precedence, 
than the addition one. In the second example, we have used square brackets to change the order of
precedence. So the second expressions evaluates to 40. 
</p>

<p>
Here we put again the list of operators in SQLite. 
</p>

<pre class="code">
unary + - ~ NOT
||
*    /    %
+    -
&lt;&lt;   &lt;&gt;   &amp;    |
&lt;    &lt;=   &gt;    &gt;=
=    ==   !=   &lt;&gt;   IS  IN  LIKE  GLOB  BETWEEN
AND   
OR
</pre>

<p>
The operators on the same row have the same level of precedence. 
The precedence grows from bottom to top. 
</p>


<h2>Associativity</h2>

<p>
Sometimes the precedence is not satisfactory to determine the outcome 
of an expression. There is another rule called
<b>associativity</b>. The associativity of operators determines the 
order of evaluation of operators with the <b>same</b>
precedence level. 
</p>

<pre>
9 / 3 * 3
</pre>

<p>
What is the outcome of this expression? 9 or 1? The multiplication, 
deletion and the modulo operator
are left to right associated. So the expression is evaluated this 
way: <code>(9 / 3) * 3</code> and the result is 9.
</p>

<pre class="code">
sqlite> SELECT 9 / 3 * 3;
9
</pre>

<p>
The associativity rule is left to right. 
</p>

<pre class="code">
sqlite> SELECT 0 AND 0 OR 1;
1
</pre>

<p>
The associativity rule is again left to right. If it was right to left, the result 
would be 0.
</p>

<p>
Arithmetic, boolean, relational and bitwise operators are all left to right associated. 
</p>


<p>
In this part of the SQLite tutorial, we have covered the SQLite expressions. 
</p>

<div class="center"> 
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* horizontal */
google_ad_slot = "1734478269";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 
</div> 
<br>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified January 7, 2013  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>
